package com.kyivmohyla.kma2.dao;

import com.kyivmohyla.kma2.dao.mapper.ServiceRowMapper;
import com.kyivmohyla.kma2.model.Service;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import java.util.List;

public class ServiceDaoJdbc extends JdbcDaoSupport implements ServiceDao {

    @Override
    public Service getById(int id) throws DataAccessException {
        return getJdbcTemplate()
                .queryForObject(
                        "SELECT service.service_id, service.name, service.is_parent, service.parent_id, service.description FROM service WHERE service.service_id = ?",
                        new Object[]{id}, new ServiceRowMapper());
    }

    @Override
    public List<Service> getAll() throws DataAccessException {
        return getJdbcTemplate()
                .query("SELECT service.service_id, service.name, service.is_parent, service.parent_id, service.description FROM service ORDER BY service.service_id",
                        new ServiceRowMapper());
    }

    @Override
    public void create(Service service) throws DataAccessException {
        getJdbcTemplate()
                .update("INSERT INTO service (service.service_id, service.name, service.is_parent, service.parent_id, service.description) VALUES (serv_equip_seq.NEXTVAL, ?, ?, ?, ?)",
                        service.getName(),
                        service.getIsParent(),
                        service.getParentId() == 0 ? null : service
                                .getParentId(),
                        service.getDescription());
    }

    @Override
    public void update(Service service) throws DataAccessException {
        getJdbcTemplate()
                .update("UPDATE service SET service.name = ?, service.is_parent = ?, service.parent_id = ?, service.description = ? WHERE service.service_id = ?",
                        service.getName(),
                        service.getIsParent(),
                        service.getParentId() == 0 ? null : service
                                .getParentId(),
                        service.getDescription(),
                        service.getServiceId());
    }

    @Override
    public void delete(Service service) throws DataAccessException {
        getJdbcTemplate().update(
                "DELETE FROM service WHERE service.service_id = ?",
                service.getServiceId());
    }

    @Override
    public void deleteList(List<Service> objs) throws DataAccessException {
        for (Service service : objs) {
            delete(service);
        }
    }

    @Override
    public boolean canBeChildDeleted(Service service) throws DataAccessException {
        List<Service> couldNoBeDeletedList = getJdbcTemplate().query("SELECT service.service_id, service.name, service.is_parent, service.parent_id, service.description FROM service WHERE service.is_parent = 0 AND service.service_id in (SELECT order_serv_equip.serv_equip_id FROM order_serv_equip UNION SELECT refs.service_id FROM refs)",
                new ServiceRowMapper());
        return !couldNoBeDeletedList.contains(service);
    }

    @Override
    public boolean canBeParentDeleted(Service service) throws DataAccessException {
        List<Service> children = getJdbcTemplate().query("SELECT service.service_id, service.name, service.is_parent, service.parent_id, service.description FROM service WHERE service.parent_id = ?", new Object[]{service.getServiceId()}, new ServiceRowMapper());
        return children.size() == 0;
    }

    @Override
    public List<Service> getServiceByOrderId(int id) throws DataAccessException {
        return getJdbcTemplate().query("Select service.service_id, service.name, service.is_parent, service.parent_id, service.description From service Where service.service_id IN (Select order_serv_equip.serv_equip_id from order_serv_equip where order_serv_equip.order_id = ?)", new Object[]{id}, new ServiceRowMapper());
    }
}
